﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace ProcessoSeletivo.DAO
{
    public class Candidato : IDisposable
    {
        #region " Listar "

        public DataView ListarCandidato()
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_candidato");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Consultar "

        public DataView ConsultarCandidato(int cod_candidato_can)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ConsultarCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_candidato");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        public DataView ConsultarAcompanhamentoCandidato(string num_cpf_can, string des_processo_pro)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ConsultarAcompanhamentoCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@num_cpf_can", num_cpf_can);
                sqlc.Parameters.AddWithValue("@des_processo_pro", des_processo_pro);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_candidato");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Incluir "

        public int IncluirCandidato(DataSet dsRegistro)
        {
            int PrimaryKey = 0;

            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("IncluirCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;                

                sqlc.Parameters.AddWithValue("@nom_candidato_can", dsRegistro.Tables[0].Rows[0]["nom_candidato_can"]);                
                sqlc.Parameters.AddWithValue("@dat_nascimento_can", dsRegistro.Tables[0].Rows[0]["dat_nascimento_can"]);
                sqlc.Parameters.AddWithValue("@num_rg_can", dsRegistro.Tables[0].Rows[0]["num_rg_can"]);
                sqlc.Parameters.AddWithValue("@des_emissor_rg_can", dsRegistro.Tables[0].Rows[0]["des_emissor_rg_can"]);
                sqlc.Parameters.AddWithValue("@nom_uf_rg_est", dsRegistro.Tables[0].Rows[0]["nom_uf_rg_est"]);
                sqlc.Parameters.AddWithValue("@num_cpf_can", dsRegistro.Tables[0].Rows[0]["num_cpf_can"]);
                sqlc.Parameters.AddWithValue("@des_sexo_can", dsRegistro.Tables[0].Rows[0]["des_sexo_can"]);
                sqlc.Parameters.AddWithValue("@num_telefone_can", dsRegistro.Tables[0].Rows[0]["num_telefone_can"]);
                sqlc.Parameters.AddWithValue("@num_celular_can", dsRegistro.Tables[0].Rows[0]["num_celular_can"]);
                sqlc.Parameters.AddWithValue("@des_email_can", dsRegistro.Tables[0].Rows[0]["des_email_can"]);
                sqlc.Parameters.AddWithValue("@nom_colegio_can", dsRegistro.Tables[0].Rows[0]["nom_colegio_can"]);
                sqlc.Parameters.AddWithValue("@num_ano_conclusao_can", dsRegistro.Tables[0].Rows[0]["num_ano_conclusao_can"]);
                sqlc.Parameters.AddWithValue("@num_inscricao_enem_can", dsRegistro.Tables[0].Rows[0]["num_inscricao_enem_can"]);
                sqlc.Parameters.AddWithValue("@num_ano_enem_can", dsRegistro.Tables[0].Rows[0]["num_ano_enem_can"]);                
                sqlc.Parameters.AddWithValue("@cod_cidade_colegio_cid", dsRegistro.Tables[0].Rows[0]["cod_cidade_colegio_cid"]);
                sqlc.Parameters.AddWithValue("@flg_aprovado_can", dsRegistro.Tables[0].Rows[0]["flg_aprovado_can"]);
                sqlc.Parameters.AddWithValue("@tip_lingua_estrangeira_can", dsRegistro.Tables[0].Rows[0]["tip_lingua_estrangeira_can"]);                
                sqlc.Parameters.AddWithValue("@num_nota_can", dsRegistro.Tables[0].Rows[0]["num_nota_can"]);
                sqlc.Parameters.AddWithValue("@cod_convenio_con", dsRegistro.Tables[0].Rows[0]["cod_convenio_con"]);
                sqlc.Parameters.AddWithValue("@cod_endereco_end", dsRegistro.Tables[0].Rows[0]["cod_endereco_end"]);
                sqlc.Parameters.AddWithValue("@num_nota_redacao_can", dsRegistro.Tables[0].Rows[0]["num_nota_redacao_can"]);
                
                PrimaryKey = Convert.ToInt32(sqlc.ExecuteScalar().ToString());
                sql.Close();
            }

            return PrimaryKey;
        }

        #endregion

        #region " Alterar "

        public void AlterarCandidato(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@nom_candidato_can", dsRegistro.Tables[0].Rows[0]["nom_candidato_can"]);
                sqlc.Parameters.AddWithValue("@dat_nascimento_can", dsRegistro.Tables[0].Rows[0]["dat_nascimento_can"]);
                sqlc.Parameters.AddWithValue("@num_rg_can", dsRegistro.Tables[0].Rows[0]["num_rg_can"]);
                sqlc.Parameters.AddWithValue("@des_emissor_rg_can", dsRegistro.Tables[0].Rows[0]["des_emissor_rg_can"]);
                sqlc.Parameters.AddWithValue("@nom_uf_rg_est", dsRegistro.Tables[0].Rows[0]["nom_uf_rg_est"]);
                sqlc.Parameters.AddWithValue("@num_cpf_can", dsRegistro.Tables[0].Rows[0]["num_cpf_can"]);
                sqlc.Parameters.AddWithValue("@des_sexo_can", dsRegistro.Tables[0].Rows[0]["des_sexo_can"]);
                sqlc.Parameters.AddWithValue("@num_telefone_can", dsRegistro.Tables[0].Rows[0]["num_telefone_can"]);
                sqlc.Parameters.AddWithValue("@num_celular_can", dsRegistro.Tables[0].Rows[0]["num_celular_can"]);
                sqlc.Parameters.AddWithValue("@des_email_can", dsRegistro.Tables[0].Rows[0]["des_email_can"]);
                sqlc.Parameters.AddWithValue("@nom_colegio_can", dsRegistro.Tables[0].Rows[0]["nom_colegio_can"]);
                sqlc.Parameters.AddWithValue("@num_ano_conclusao_can", dsRegistro.Tables[0].Rows[0]["num_ano_conclusao_can"]);
                sqlc.Parameters.AddWithValue("@num_inscricao_enem_can", dsRegistro.Tables[0].Rows[0]["num_inscricao_enem_can"]);
                sqlc.Parameters.AddWithValue("@num_ano_enem_can", dsRegistro.Tables[0].Rows[0]["num_ano_enem_can"]);
                sqlc.Parameters.AddWithValue("@cod_cidade_colegio_cid", dsRegistro.Tables[0].Rows[0]["cod_cidade_colegio_cid"]);
                sqlc.Parameters.AddWithValue("@flg_aprovado_can", dsRegistro.Tables[0].Rows[0]["flg_aprovado_can"]);
                sqlc.Parameters.AddWithValue("@tip_lingua_estrangeira_can", dsRegistro.Tables[0].Rows[0]["tip_lingua_estrangeira_can"]);
                sqlc.Parameters.AddWithValue("@num_nota_can", dsRegistro.Tables[0].Rows[0]["num_nota_can"]);
                sqlc.Parameters.AddWithValue("@cod_convenio_con", dsRegistro.Tables[0].Rows[0]["cod_convenio_con"]);
                sqlc.Parameters.AddWithValue("@cod_endereco_end", dsRegistro.Tables[0].Rows[0]["cod_endereco_end"]);
                sqlc.Parameters.AddWithValue("@num_nota_redacao_can", dsRegistro.Tables[0].Rows[0]["num_nota_redacao_can"]);
                sqlc.Parameters.AddWithValue("@cod_candidato_can", dsRegistro.Tables[0].Rows[0]["cod_candidato_can"]);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        public void AlterarCandidatoNotaRedacao(int cod_candidato_can, double num_nota_redacao_can)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarCandidatoNotaRedacao", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                sqlc.Parameters.AddWithValue("@num_nota_redacao_can", num_nota_redacao_can);
                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);
                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        public void AlterarCandidatoNotaFinal(int cod_candidato_can, double num_nota_can)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarCandidatoNotaFinal", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                sqlc.Parameters.AddWithValue("@num_nota_can", num_nota_can);
                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);
                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        #endregion

        #region " Excluir "

        public void ExcluirCandidato(int cod_candidato_can)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ExcluirCandidato", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_candidato_can", cod_candidato_can);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        #endregion

        #region " Dipose "

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
